﻿using KPIS.GERP.Models;
using KPIS.GERP.Models.Class;
using KPIS.GERP.Models.ICM;
using KPIS.GERP.Models.SYS;
using KPIS.GERP.Models.FIM;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using KPIS.GERP.Models.ICM.ViewModel;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Linq.Dynamic;
using System.ServiceModel;
using System.Web.Mvc;
using System.Collections;

namespace KPIS.GERP.WCF.GIMS
{
    [ServiceContract]
    public interface IBidService
    {
        #region Bid
        [OperationContract]
        IEnumerable<Bid> SelectBid(Bid criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        int InsertBid(Bid model, int createByUserId);

        [OperationContract]
        void UpdateBid(Bid model, int updateByUserId);

        [OperationContract]
        void DeleteBid(int id, int deleteByUserId);
        #endregion

        #region Bidder
        [OperationContract]
        IEnumerable<Bidder> SelectBidder(Bidder criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        IEnumerable<SelectListItem> SelectListBidder(Bidder criteriaModel = null);

        [OperationContract]
        int InsertBidder(Bidder model, int createByUserId);

        [OperationContract]
        void UpdateBidder(Bidder model, int updateByUserId);

        [OperationContract]
        void DeleteBidder(int id, int deleteByUserId);
        #endregion

        #region BidderCollateral
        [OperationContract]
        IEnumerable<BidderCollateral> SelectBidderCollateral(BidderCollateral criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        IEnumerable<SelectListItem> SelectListBidderCollateral(BidderCollateral criteriaModel = null);

        [OperationContract]
        List<GuarateeContract> SelectBidderCollateralGuarantee(DateTime? dateReturn, int ColStatusId);

        [OperationContract]
        IEnumerable<GuarateeContract> SelectListBidderCollateralGuarantee(DateTime? dateReturn, int ColStatusId, int Id);

        [OperationContract]
        IEnumerable<GuarateeContract> SelectBidderCollateralGuaranteeName(DateTime? dateReturn, int ColStatusId, int Id);

        [OperationContract]
        int InsertBidderCollateral(BidderCollateral model, int createByUserId);

        [OperationContract]
        void UpdateBidderCollateral(BidderCollateral model, int updateByUserId);

        [OperationContract]
        void UpdateBidderCollateralGuarantee(int Id, string StatusBidder);

        [OperationContract]
        void DeleteBidderCollateral(int id, int deleteByUserId);
        #endregion

        #region BidderQuotation
        [OperationContract]
        IEnumerable<BidderQuotation> SelectBidderQuotation(BidderQuotation criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        IEnumerable<SelectListItem> SelectListBidderQuotation(BidderQuotation criteriaModel = null);

        [OperationContract]
        IEnumerable<BidderQuotation> SelectBidderQuotationForSelection(int BidId);

        [OperationContract]
        int InsertBidderQuotation(BidderQuotation model, int createByUserId);

        [OperationContract]
        void UpdateBidderQuotation(BidderQuotation model, int updateByUserId);

        [OperationContract]
        void DeleteBidderQuotation(int id, int deleteByUserId);

        [OperationContract]
        int SumQuantity(int bidderQuoId, int? PoInstallId);

        [OperationContract]
        decimal SumAmount(int bidderQuoId, int? PoInstallId);

        [OperationContract]
        IEnumerable<BidderQuotation> SelectBidderQuotationWithRemainQty(int BidderId, int PoInstallId);

        #endregion

        [OperationContract]
        string[] ApproveName(int BidId);

        #region Report

        [OperationContract]
        string[] OfferReport(int BidId);

        [OperationContract]
        List<ReportCollateral> CollateralReport(DateTime StartDate, DateTime EndDate);

        #endregion

    }

    public class BidService : IBidService
    {
        #region Bid

        public IEnumerable<Bid> SelectBid(Bid criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<Bid> query = context.Bids;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    return x;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int InsertBid(Bid model, int createByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.RecordStatus = RecordStatus.WaitStatus;
                    model.CreatedBy = createByUserId;
                    model.CreatedWhen = DateTime.Now;
                    model.Code = CodeGenerate.GetNextCode(CodeGenerate.TransectionType.Bid, "");
                    context.Bids.Add(model);
                    context.SaveChanges();

                    return model.BidId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateBid(Bid model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteBid(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var modelToDelete = context.Bids.Find(id);
                    if (modelToDelete == null) return;

                    context.Entry(modelToDelete).State = EntityState.Modified;
                    modelToDelete.RecordStatus = RecordStatus.DeletedStatus;
                    modelToDelete.UpdatedBy = deleteByUserId;
                    modelToDelete.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region Bidder
        public IEnumerable<Bidder> SelectBidder(Bidder criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<Bidder> query = context.Bidders;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    return x;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<SelectListItem> SelectListBidder(Bidder criteriaModel = null)
        {
            var list = new List<SelectListItem>();
            list.Add(new SelectListItem { Selected = true, Text = "ไม่ระบุ", Value = "" });

            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<Bidder> query = context.Bidders.Include("MasContact");

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    foreach (var model in x)
                    {
                        var item = new SelectListItem { Text = model.MasContact.NameTh, Value = model.BidderId.ToString() };
                        list.Add(item);
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }

        public int InsertBidder(Bidder model, int createByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.CreatedBy = createByUserId;
                    model.CreatedWhen = DateTime.Now;
                    //model. = CodeGenerate.GetNextCode(CodeGenerate.TransectionType.Bidder);
                    context.Bidders.Add(model);
                    context.SaveChanges();

                    return model.BidderId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateBidder(Bidder model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteBidder(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var modelToDelete = context.Bidders.Find(id);
                    if (modelToDelete == null) return;

                    context.Bidders.Remove(modelToDelete);
                    context.SaveChanges();
                }
            }           
            catch (Exception ex)
            {
                //-2146233087
                throw ex;
            }
        }
        #endregion

        #region BidderCollateral
        public IEnumerable<BidderCollateral> SelectBidderCollateral(BidderCollateral criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<BidderCollateral> query = context.BidderCollaterals;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    return x;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<GuarateeContract> SelectBidderCollateralGuarantee(DateTime? dateReturn, int ColStatusId)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    var query = (from bidderA in context.BidderCollaterals
                                 join bidderB in context.PurchaseOrders on bidderA.BidderId equals bidderB.BidderId into bidderBs
                                 from bidderB in bidderBs.DefaultIfEmpty()
                                 join bidderC in context.Bidders on bidderA.BidderId equals bidderC.BidderId into bidderCs
                                 from bidderC in bidderCs.DefaultIfEmpty()
                                 join bidderD in context.Bids on bidderC.BidId equals bidderD.BidId into bidderDs
                                 from bidderD in bidderDs.DefaultIfEmpty()
                                 join bidderE in context.IcmMasLookUps on bidderA.ColTypeId equals bidderE.LookUpId into bidderEs
                                 from bidderE in bidderEs.DefaultIfEmpty()
                                 join bidderF in context.IcmMasLookUps on bidderA.ColCategoryId equals bidderF.LookUpId
                                 where bidderA.ColStatusId == ColStatusId
                                 && bidderA.ReturnDate <= dateReturn
                                 select new GuarateeContract()
                                 {
                                     CodePo = bidderB.Code,
                                     CodeBids = bidderD.Code,
                                     BidsName = bidderD.Name,
                                     NamePo = bidderB.Name,
                                     SoftName = bidderF.LookUpName,
                                     ProPretise = bidderE.LookUpName,
                                     ReturnDate = bidderA.ReturnDate,
                                     Amount = bidderA.Amount,
                                     CompleDate = bidderD.DiscloseDate,
                                     BidderColId = bidderA.BidderColId,
                                     ColCategoryId = bidderA.ColCategoryId,
                                     ColTypeId = bidderA.ColTypeId,
                                     Code = bidderA.Code
                                 }).ToList();

                   return query;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<GuarateeContract> SelectListBidderCollateralGuarantee(DateTime? dateReturn, int ColStatusId, int Id)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IEnumerable<GuarateeContract> query = (from bidderA in context.BidderCollaterals
                                                          join bidderB in context.PurchaseOrders on bidderA.BidderId equals bidderB.BidderId into bidderBs
                                                          from bidderB in bidderBs.DefaultIfEmpty()
                                                          join bidderC in context.Bidders on bidderA.BidderId equals bidderC.BidderId into bidderCs
                                                          from bidderC in bidderCs.DefaultIfEmpty()
                                                          join bidderD in context.Bids on bidderC.BidId equals bidderD.BidId into bidderDs
                                                          from bidderD in bidderDs.DefaultIfEmpty()
                                                          join bidderE in context.IcmMasLookUps on bidderA.ColTypeId equals bidderE.LookUpId into bidderEs
                                                          from bidderE in bidderEs.DefaultIfEmpty()
                                                          join bidderF in context.IcmMasLookUps on bidderA.ColCategoryId equals bidderF.LookUpId
                                                          where bidderA.ColStatusId == ColStatusId
                                                          && bidderA.ReturnDate <= dateReturn && bidderA.ColCategoryId == Id
                                                          select new GuarateeContract
                                                          {
                                                              CodePo = bidderB.Code,
                                                              CodeBids = bidderD.Code,
                                                              BidsName = bidderD.Name,
                                                              NamePo = bidderB.Name,
                                                              SoftName = bidderF.LookUpName,
                                                              ProPretise = bidderE.LookUpName,
                                                              ReturnDate = bidderA.ReturnDate,
                                                              Amount = bidderA.Amount,
                                                              CompleDate = bidderD.DiscloseDate,
                                                              BidderColId = bidderA.BidderColId,
                                                              ColCategoryId = bidderA.ColCategoryId,
                                                              ColTypeId = bidderA.ColTypeId,
                                                              Code = bidderA.Code
                                                          }).ToList();

                    return query;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public IEnumerable<GuarateeContract> SelectBidderCollateralGuaranteeName(DateTime? dateReturn, int ColStatusId, int Id)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IEnumerable<GuarateeContract> query = (from bidderA in context.BidderCollaterals
                                                          join bidderB in context.PurchaseOrders on bidderA.BidderId equals bidderB.BidderId into bidderBs
                                                          from bidderB in bidderBs.DefaultIfEmpty()
                                                          join bidderC in context.Bidders on bidderA.BidderId equals bidderC.BidderId into bidderCs
                                                          from bidderC in bidderCs.DefaultIfEmpty()
                                                          join bidderD in context.Bids on bidderC.BidId equals bidderD.BidId into bidderDs
                                                          from bidderD in bidderDs.DefaultIfEmpty()
                                                          join bidderE in context.IcmMasLookUps on bidderA.ColTypeId equals bidderE.LookUpId into bidderEs
                                                          from bidderE in bidderEs.DefaultIfEmpty()
                                                          join bidderF in context.IcmMasLookUps on bidderA.ColCategoryId equals bidderF.LookUpId
                                                          where bidderA.ColStatusId == ColStatusId
                                                          && bidderA.ReturnDate <= dateReturn && bidderA.BidderColId == Id
                                                          select new GuarateeContract
                                                          {
                                                              CodePo = bidderB.Code,
                                                              CodeBids = bidderD.Code,
                                                              BidsName = bidderD.Name,
                                                              NamePo = bidderB.Name,
                                                              SoftName = bidderF.LookUpName,
                                                              ProPretise = bidderE.LookUpName,
                                                              ReturnDate = bidderA.ReturnDate,
                                                              Amount = bidderA.Amount,
                                                              CompleDate = bidderD.DiscloseDate,
                                                              BidderColId = bidderA.BidderColId,
                                                              ColCategoryId = bidderA.ColCategoryId,
                                                              ColTypeId = bidderA.ColTypeId,
                                                              Code = bidderA.Code
                                                          }).ToList();

                    return query;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<SelectListItem> SelectListBidderCollateral(BidderCollateral criteriaModel = null)
        {
            var list = new List<SelectListItem>();
            list.Add(new SelectListItem { Selected = true, Text = "ไม่ระบุ", Value = "" });

            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<BidderCollateral> query = context.BidderCollaterals.Include("CollateralType");

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    foreach (var model in x)
                    {
                        var item = new SelectListItem { 
                            Text = model.CollateralType.LookUpName + " [" + (model.Amount.HasValue ? model.Amount.Value.ToString("#,##0.00") + " บาท" : "ฟรี" + "]")
                            , Value = model.BidderColId.ToString() };
                        list.Add(item);
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }

        public int InsertBidderCollateral(BidderCollateral model, int createByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.CreatedBy = createByUserId;
                    model.CreatedWhen = DateTime.Now;
                    context.BidderCollaterals.Add(model);
                    context.SaveChanges();

                    return model.BidderColId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateBidderCollateral(BidderCollateral model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateBidderCollateralGuarantee(int Id, string StatusBidder)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    if (StatusBidder == "คืน")
                    {
                        var query = (from BidderCollaterals in context.BidderCollaterals
                                     where BidderCollaterals.BidderColId == Id
                                     select BidderCollaterals
                      ).FirstOrDefault();

                        context.Entry(query).State = EntityState.Modified;
                        query.ColStatusId = 11;
                        context.SaveChanges();
                    }
                    else if (StatusBidder == "จ่าย")
                    {
                        var query = (from BidderCollaterals in context.BidderCollaterals
                                     where BidderCollaterals.BidderColId == Id
                                     select BidderCollaterals
                     ).FirstOrDefault();

                        context.Entry(query).State = EntityState.Modified;
                        query.ColTypeId = 74;
                        context.SaveChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteBidderCollateral(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var modelToDelete = context.BidderCollaterals.Find(id);
                    if (modelToDelete == null) return;

                    context.BidderCollaterals.Remove(modelToDelete);
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region BidderQuotation
        public IEnumerable<BidderQuotation> SelectBidderQuotation(BidderQuotation criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<BidderQuotation> query = context.BidderQuotations;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    return x;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<SelectListItem> SelectListBidderQuotation(BidderQuotation criteriaModel = null)
        {
            var list = new List<SelectListItem>();
            list.Add(new SelectListItem { Selected = true, Text = "ไม่ระบุ", Value = "" });

            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<BidderQuotation> query = context.BidderQuotations.Include("PurchaseRequestItem.MasResourceItem");

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    foreach (var model in x)
                    {
                        var item = new SelectListItem
                        {
                            Text = model.PurchaseRequestItem.MasResourceItem.Name
                                + " [" + (model.NetAmount.HasValue ? model.NetAmount.Value.ToString("#,##0.00") : "0.00") + " บาท]"
                            , Value = model.BidderQuoId.ToString()
                        };
                        list.Add(item);
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }

        public IEnumerable<BidderQuotation> SelectBidderQuotationForSelection(int BidId)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    var model = context.BidderQuotations
                        .Include("Bidder")
                        .Include("PurchaseRequestItem.MasResourceItem")
                        .Include("PurchaseRequestItem.MasBudgetExpense")
                        .Where(w => w.Bidder.BidId == BidId)
                        .ToList();
                    return model;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int InsertBidderQuotation(BidderQuotation model, int createByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.CreatedBy = createByUserId;
                    model.CreatedWhen = DateTime.Now;
                    context.BidderQuotations.Add(model);
                    context.SaveChanges();

                    return model.BidderQuoId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateBidderQuotation(BidderQuotation model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteBidderQuotation(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var modelToDelete = context.BidderQuotations.Find(id);
                    if (modelToDelete == null) return;

                    context.BidderQuotations.Remove(modelToDelete);
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<BidderQuotation> SelectBidderQuotationWithRemainQty(int BidderId, int PoInstallId)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<BidderQuotation> query = (from bd in context.Bidders
                                                         join bdq in context.BidderQuotations on bd.BidderId equals bdq.BidderId into bdqs
                                                         from bdq in bdqs.DefaultIfEmpty()
                                                         join pod in context.PurchaseOrderInstallmentItems on bdq.BidderQuoId equals pod.BidderQuoId into pods
                                                         from pod in pods.DefaultIfEmpty()
                                                         where bdq.BidderId == BidderId
                                                         && pod.PoInstallId == PoInstallId
                                                         select bdq);

                    //var results =  query.GroupBy(bdq => bdq.BidderQuoId, bdq => bdq.BidderId );
                    var x = query.ToList();
                    return x;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion


        public int SumQuantity(int bidderQuoId, int? PoInstallId)
        {
            int Quantity = 0;
            int MaxQuantity = 0;
            int result = 0;
            
            try
            {
                using (var context = new GERPContext())
                {
                    var ListQuantity = (from a in context.PurchaseOrderInstallmentItems
                                        join b in context.PurchaseOrderInstallments on a.PoInstallId equals b.PoInstallId
                                        join c in context.PurchaseOrders on b.PoId equals c.PoId
                                        where a.BidderQuoId == bidderQuoId
                                        && c.RecordStatus != RecordStatus.DeletedStatus
                                        && (PoInstallId.HasValue ? a.PoInstallId != PoInstallId : true)
                                        select a);
                    
                    Quantity = ListQuantity.Sum(a => a.Qty) ?? 0;

                    MaxQuantity = (from b in context.BidderQuotations
                                        where b.BidderQuoId == bidderQuoId
                                        select b).FirstOrDefault().Qty ?? 0;

                    result = MaxQuantity - Quantity;

                    return result;

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public decimal SumAmount(int bidderQuoId, int? PoInstallId)
        {
            decimal Amount = 0;
            decimal MaxAmount = 0;
            decimal result = 0;

            try
            {
                using (var context = new GERPContext())
                {
                    var ListQuantity = (from a in context.PurchaseOrderInstallmentItems
                                        join b in context.PurchaseOrderInstallments on a.PoInstallId equals b.PoInstallId
                                        join c in context.PurchaseOrders on b.PoId equals c.PoId
                                        where a.BidderQuoId == bidderQuoId
                                        && c.RecordStatus != RecordStatus.DeletedStatus
                                        && (PoInstallId.HasValue ? a.PoInstallId != PoInstallId : true)
                                        select a);

                    Amount = Convert.ToDecimal(ListQuantity.Sum(a => a.NetAmount) ?? 0);

                    MaxAmount = (from b in context.BidderQuotations
                                   where b.BidderQuoId == bidderQuoId
                                   select b).FirstOrDefault().NetAmount ?? 0;

                    result = MaxAmount - Amount;

                    return result;

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string[] ApproveName(int BidId)
        {
            GERPContext context = new GERPContext();
            string name = "";
            string posname = "";

            //var x = (from a in context.ApprovalTaskDtls
            //         join b in context.ApprovalTasks on a.ApprovalTaskId equals b.ApprovalTaskId
            //         join c in context.Bids on b.RefId equals c.BidId
            //         join d in context.Employees on b.UpdatedBy equals d.EmpId
            //         join e in context.Positions on d.PosId equals e.PosId
            //         where b.OriginalAppPrcId == 12      // 12 หมายถึง ขออนุมัติประกาศ
            //         && b.RefId == BidId
            //         select new {name=d.FirstName + "   "+d.LastName, posname = e.Name}).FirstOrDefault();
            
            var x = (from a in context.TaskHistories
                     join b in context.Tasks on a.TaskId equals b.TaskId
                     join c in context.Bids on b.RefId equals c.BidId
                     join d in context.Employees on b.UpdatedBy equals d.EmpId
                     join e in context.Positions on d.PosId equals e.PosId
                     where b.AppPrcId == 12      // 12 หมายถึง ขออนุมัติประกาศ
                     && b.RefId == BidId
                     select new {name=d.FirstName + "   "+d.LastName, posname = e.Name}).FirstOrDefault();

            if (x != null)
            {
                name = x.name;
                posname = x.posname;
            }

            return new string[] {name,posname};
        }

        #region Report

        public string[] OfferReport(int BidId)
        {
            string PrName = "";
            string OfferDateStart = "";
            string OfferDateEnd = "";
            string AnnounceDate = "";

            using (var context = new GERPContext(false))
            {
                var x = (from a in context.Bidders
                             join b in context.Bids on a.BidId equals b.BidId
                             join c in context.PurchaseRequests on b.PrId equals c.PrId
                             join d in context.MasContacts on a.ConId equals d.ConId
                             where a.BidId == BidId 
                             select new { PrName = c.Name, b.OfferDateStart, b.OfferDateEnd, b.AnnounceDate}).FirstOrDefault();
                if(x != null)
                {
                    PrName = x.PrName;
                    OfferDateStart = x.OfferDateStart != null ? x.OfferDateStart.Value.ToShortDateString() : "";
                    OfferDateEnd = x.OfferDateEnd != null ? x.OfferDateEnd.Value.ToShortDateString() : "";
                    AnnounceDate = x.AnnounceDate != null ? x.AnnounceDate.Value.ToShortDateString() : "";
                }
            }
            return new string[] {PrName, OfferDateStart, OfferDateEnd, AnnounceDate};
        }

        public List<ReportCollateral> CollateralReport(DateTime StartDate, DateTime EndDate)
        {
            // move to GuaranteeContractController
            using (GERPContext context = new GERPContext())
            {
                var modelCol = context.BidderCollaterals
                    .Include("Bidder.MasContact").Include("CollateralType")
                    .ToList()
                    .Where(
                        m => 
                            m.ReturnDate != null &&
                            (m.ReturnDate.Value.Date >= StartDate.Date && m.ReturnDate.Value.Date <= EndDate.Date));


                var models = new List<ReportCollateral>();

                int p = 1;

                foreach (var item in modelCol)
                {
                    var modelMR = new PayRequest();
                    var modelPay = new Pay();
                    var modelPayCheque = new PayCheque();
                    var model = new ReportCollateral();

                    var modelPO = context.PurchaseOrders.FirstOrDefault(m => m.BidderId == item.BidderId);
                    modelMR = context.PayRequests.FirstOrDefault(m => m.BidderColId == item.BidderColId);
                    if (modelMR != null &&
                        (modelPay = context.Pays.FirstOrDefault(m => m.PayReqId == modelMR.PayReqId)) != null)
                    {
                        modelPayCheque =
                            context.PayCheques.Include("Cheque").FirstOrDefault(m => m.PayId == modelPay.PayId);
                    }

                    model.No = p++;
                    model.PoCode = modelPO != null ? modelPO.Code : item.Code;
                    model.PoDate = modelPO != null && modelPO.OrderDate.HasValue
                        ? modelPO.OrderDate.Value.ToShortDateString()
                        : null;
                    model.PoName = modelPO != null ? modelPO.Name : null;
                    model.ContactName = item.Bidder != null && item.Bidder.MasContact != null ? item.Bidder.MasContact.NameTh : null;
                    model.SumPoItemAmount =
                        (modelPO != null
                            ? context.PurchaseOrderItems.Where(m => m.PoId == modelPO.PoId)
                                .Include("BidderQuotation")
                                .Sum(m => m.BidderQuotation.Price*m.BidderQuotation.Qty) ?? 0
                            : 0).ToString("#,##0.00");
                    model.CollteralTypeName = item.CollateralType != null ? item.CollateralType.LookUpName : null;
                    model.CollteralAmount = (item.Amount ?? 0).ToString("#,##0.00");//.ToString("#,##0.00");
                    model.DeliveryDate = modelPO != null && modelPO.DeliveryDate.HasValue
                        ? modelPO.DeliveryDate.Value.ToShortDateString()
                        : null;
                    model.ReturnDate = item.ReturnDate.HasValue ? item.ReturnDate.Value.ToShortDateString() : "";
                    model.MoneyReqRefNo = modelMR != null ? modelMR.Code : null;
                    model.MoneyReqDate = modelMR != null ? modelMR.PayReqDate.Value.ToShortDateString() : null;
                    model.ApproverName = "";
                    model.ChequeNo = modelPayCheque.Cheque != null ? modelPayCheque.Cheque.ChequeNo.ToString() : null;
                    model.ChequeDate = modelPayCheque.Cheque != null
                        ? modelPayCheque.Cheque.ChequeDate.Value.ToShortDateString()
                        : null;
                    model.ChequeStatus = modelPayCheque.Cheque != null ? modelPayCheque.Cheque.ChequeStatus : null;

                    models.Add(model); 
                }
                return models;
            }
        }

        #endregion

    }
}
